<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class StatisticsAttendDept extends AdminController {

	function __construct() {
		parent::__construct();
		$this-> load->model('Attend_model');
		$this-> load->model('Select_model');
	}

	function Index(){
		// 听课类别-0727
		$result=$this -> Select_model -> get_list(array('type'=>SelectType::ListenType[XPHEnum::Value],'status'=>CommonStatus::UnDeleted[XPHEnum::Value]));
		$data['listen_type']=array();
		foreach ($result as $k => $v) {
			array_push($data['listen_type'],$v['option']);
		}
		$role=explode(',',getSess()['sub_role_id']);
		array_push($role,getSess()['role_id']);	
		if(in_array(WebRole::Admin[XPHEnum::Value], $role) || in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role)||in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)||in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role)||in_array(WebRole::StudentInfoOfficer[XPHEnum::Value], $role)||in_array(WebRole::UnitStudentInfoOfficer[XPHEnum::Value], $role)||in_array(WebRole::UnitLeader[XPHEnum::Value], $role)||in_array(WebRole::SupervisorDirector[XPHEnum::Value], $role)||in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)||in_array(WebRole::Manage[XPHEnum::Value], $role)||in_array(WebRole::OutsideSupervisor[XPHEnum::Value], $role)||in_array(WebRole::MajorUnitLeader[XPHEnum::Value], $role)){
			$data['is_show'] = 1;
			
		}else{
			
			$data['is_show'] = 0;
		}	
		$this->load->view('admin/statisticsAttendDeptList',$data);
	}

	function getDatas($params = array(), $isExport = false){
			$connar = array();
			$params = $_POST;
			if (!$isExport) {
				$connar = $this -> Attend_model -> pickPages($connar, $params);
			}			
			$dept_name = mb_substr(getSess()['dept_name'],0,2,"utf-8");             
	        if($dept_name =='财经' ||$dept_name =='商贸'||$dept_name =='交通'||$dept_name =='鉴湖'||$dept_name =='黄酒'){
	            $dept_name_1 = mb_substr(getSess()['dept_name'],0,2,"utf-8");             
	        }else if($dept_name =='建筑' ||$dept_name =='机电'||$dept_name =='交通'){
	            $dept_name_1  = mb_substr(getSess()['dept_name'],0,4,"utf-8");   
	        }else if($dept_name =='设计'){
	            $dept_name_1 = mb_substr(getSess()['dept_name'],0,5,"utf-8");   
	        }else{
	            $dept_name_1  =getSess()['dept_name'];
	        }
		
			$where=$this->sqlEscape('where status=??',array(AttendStatus::Submit[XPHEnum::Value]));
			$where1=$this->sqlEscape('and status=??',array(AttendStatus::Submit[XPHEnum::Value]));
			$role=explode(',',getSess()['sub_role_id']);
			array_push($role,getSess()['role_id']);	
			$role=array_filter($role);
			$role=array_merge($role);
			if(in_array(WebRole::Manage[XPHEnum::Value], $role)){
				// 记得解开
				// $connar['where'].=$this->sqlEscape(' and admin_type=??',array(AdminType::Auth[XPHEnum::Value]));
			}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
				//$where.=$this->sqlEscape(' and dept_name=??',array(getSess()['dept_name']));
				//查询该用户的开课单位信息
				/* $connar5['items']='distinct(course_units)';
				$connar5['where']=$this->sqlEscape('teacher_code=??',array(getSess()['usercode']));
				$dept_name_1=$this->Attend_model->get_single_full($connar5);
				if(isset($dept_name_1['course_units']) && $dept_name_1['course_units'] != ""){
				$where.=$this->sqlEscape(' and course_units=??',array($dept_name_1['course_units']));
				}else{
				$where.=$this->sqlEscape(' and course_units=??',array(str_replace('分', '学', getSess()['dept_name'])));
				}
	          */
	                $where.= " and  attend.course_units like '%". $dept_name_1."%'";
				    $where1.= " and  db_attend.course_units like '%". $dept_name_1."%'";
				// $where.=$this->sqlEscape(' and attend.course_units=??',array(str_replace('分', '学', getSess()['dept_name'])));
				// $where1.=$this->sqlEscape(' and db_attend.course_units=??',array(str_replace('分', '学', getSess()['dept_name'])));
			
			}else if(in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::OutsideSupervisor[XPHEnum::Value], $role)){
				$where.=$this->sqlEscape(' and attend.listen_id=??',array(getSess()['id']));
				$where1.=$this->sqlEscape(' and db_attend.listen_id=??',array(getSess()['id']));
			}else{
				$where.=$this->sqlEscape(' and attend.listen_id=??',array(getSess()['id']));
				$where1.=$this->sqlEscape(' and db_attend.listen_id=??',array(getSess()['id']));
			}
			
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where.=' and attend.listen_time>='.strtotime($params['start_time']).' and attend.listen_time<='.strtotime($params['end_time'].'23:59:59');
				$where1.=' and db_attend.listen_time>='.strtotime($params['start_time']).' and db_attend.listen_time<='.strtotime($params['end_time'].'23:59:59');
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$where.=' and attend.listen_time>='.strtotime($_POST['start_time']).' and attend.listen_time<='.strtotime($_POST['end_time'].'23:59:59');
				$where1.=' and db_attend.listen_time>='.strtotime($_POST['start_time']).' and db_attend.listen_time<='.strtotime($_POST['end_time'].'23:59:59');
			}

			//听课身份
			if (isset($params['class_type']) && $params['class_type'] > -1) {
				$where.=$this->sqlEscape(' and type=??',array($params['class_type']));
				$where1.=$this->sqlEscape(' and type=??',array($params['class_type']));
			}

			//听课类型
			if (isset($params['listen_type']) && $params['listen_type'] > -1) {
				$where.=$this->sqlEscape(' and listen_type=??',array($params['listen_type']));
				$where1.=$this->sqlEscape(' and listen_type=??',array($params['listen_type']));
			}
			// 督导姓名
			if (isset($params['username']) && $params['listen_type'] !='') {
				$where.=$this->sqlLikeEscape(" and listen_name like '%??%'",array($params['username']));
				$where1.=$this->sqlLikeEscape("  and listen_name like '%??%'",array($params['username']));
			}

			if (!$isExport) {
				if($params['type']==1){
					$data['ret'] = $this -> Attend_model -> query("SELECT attend.course_units, IFNULL(attend_num,0)attend_num , IFNULL(attend_count,0)attend_count , IFNULL(attend_num_school,0)attend_num_school, IFNULL(attend_count_school,0)attend_count_school, IFNULL(attend_num_outside,0)attend_num_outside, IFNULL(attend_count_outside,0)attend_count_outside,  IFNULL(attend_num_teacher,0)attend_num_teacher, IFNULL(attend_count_teacher,0)attend_count_teacher, IFNULL(avg_total_score,0)avg_total_score, IFNULL(avg_school_score,0)avg_school_score, IFNULL(avg_unit_score,0)avg_unit_score, IFNULL(avg_outside_score,0)avg_outside_score, IFNULL(avg_teacher_score,0)avg_teacher_score, concat(max_score,'(',max_teacher,')') max_score, concat(min_score,'(',min_teacher,')') min_score FROM db_attend attend LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num, course_units FROM db_attend  WHERE  type = '2' and status=2 $where1 GROUP BY course_units ) attend_num ON attend_num.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count, course_units FROM db_attend  WHERE  type = '2' and status=2 $where1 GROUP BY course_units ) attend_count ON attend_count.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1  GROUP BY course_units ) attend_num_school ON attend_num_school.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1 GROUP BY course_units ) attend_count_school ON attend_count_school.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 $where1 GROUP BY course_units ) attend_num_outside ON attend_num_outside.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 $where1 GROUP BY course_units ) attend_count_outside ON attend_count_outside.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 $where1 GROUP BY course_units ) attend_num_teacher ON attend_num_teacher.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 $where1 GROUP BY course_units ) attend_count_teacher ON attend_count_teacher.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_total_score, course_units FROM db_attend  WHERE status=2 $where1 GROUP BY course_units ) avg_total_score ON avg_total_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_school_score, course_units FROM db_attend  WHERE  type='1' and status=2 $where1 GROUP BY course_units ) avg_school_score ON avg_school_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_unit_score, course_units FROM db_attend  WHERE  type='2' and status=2 $where1 GROUP BY course_units ) avg_unit_score ON avg_unit_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_outside_score, course_units FROM db_attend  WHERE  type='3' and status=2 $where1 GROUP BY course_units ) avg_outside_score ON avg_outside_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_teacher_score, course_units FROM db_attend  WHERE  type='4' and status=2 $where1 GROUP BY course_units ) avg_teacher_score ON avg_teacher_score.course_units = attend.course_units LEFT JOIN (SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.course_units FROM ( SELECT max( score ) max_score, course_units FROM db_attend WHERE STATUS = 2 $where1 GROUP BY course_units ) a LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.course_units = a.course_units where status=2 $where1 GROUP BY db_attend.course_units) max_score ON max_score.course_units = attend.course_units LEFT JOIN (SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.course_units FROM ( SELECT min( score ) min_score, course_units FROM db_attend WHERE STATUS = 2 $where1 GROUP BY course_units ) a LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.course_units = a.course_units where status=2  $where1 GROUP BY db_attend.course_units) min_score ON min_score.course_units = attend.course_units $where GROUP BY attend.course_units ORDER BY field( dept_name, '电气电子工程分院', '机械工程分院', '建筑工程分院', '设计与艺术分院', '财经分院', '交通分院', '商贸分院', '鉴湖分院', '人文社科部')");
					
					// echo $this->db->last_query();
					$count=$this->Attend_model->query("SELECT count(*) count FROM db_attend attend LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num, course_units FROM db_attend  WHERE  type = '2' and status=2 GROUP BY course_units ) attend_num ON attend_num.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count, course_units FROM db_attend  WHERE  type = '2' and status=2 GROUP BY course_units ) attend_count ON attend_count.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_school, course_units FROM db_attend  WHERE  type = '1' and status=2 GROUP BY course_units ) attend_num_school ON attend_num_school.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_school, course_units FROM db_attend  WHERE  type = '1' and status=2 GROUP BY course_units ) attend_count_school ON attend_count_school.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 GROUP BY course_units ) attend_num_outside ON attend_num_outside.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 GROUP BY course_units ) attend_count_outside ON attend_count_outside.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 GROUP BY course_units ) attend_num_teacher ON attend_num_teacher.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 GROUP BY course_units ) attend_count_teacher ON attend_count_outside.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_total_score, course_units FROM db_attend  WHERE status=2 GROUP BY course_units ) avg_total_score ON avg_total_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_school_score, course_units FROM db_attend  WHERE  type='1' and status=2 GROUP BY course_units ) avg_school_score ON avg_school_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_unit_score, course_units FROM db_attend  WHERE  type='2' and status=2 GROUP BY course_units ) avg_unit_score ON avg_unit_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_outside_score, course_units FROM db_attend  WHERE  type='3' and status=2 GROUP BY course_units ) avg_outside_score ON avg_outside_score.course_units = attend.course_units LEFT JOIN (SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.course_units FROM ( SELECT max( score ) max_score, course_units FROM db_attend WHERE STATUS = 2 GROUP BY course_units ) a LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.course_units = a.course_units where status=2 $where1 GROUP BY db_attend.course_units) max_score ON max_score.course_units = attend.course_units LEFT JOIN (SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.course_units FROM ( SELECT min( score ) min_score, course_units FROM db_attend WHERE STATUS = 2 GROUP BY course_units ) a LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.course_units = a.course_units where status=2 GROUP BY db_attend.course_units) min_score ON min_score.course_units = attend.course_units $where GROUP BY attend.course_units ORDER BY field( dept_name, '电气电子工程分院', '机械工程分院', '建筑工程分院', '设计与艺术分院', '财经分院', '交通分院', '商贸分院', '鉴湖分院', '人文社科部')");
					$data['count']=isset($count[0]['count']) ? $count[0]['count'] : '0';
					$data["totalPage"] = ceil($data['count'] / $params['pageSize']);
					$data['pageNo'] = $params['pageNo'];	
				}else{
					$data['ret'] = $this -> Attend_model -> query("SELECT attend.listen_name username, IFNULL( attend_num, 0 ) attend_num, IFNULL( attend_count, 0 ) attend_count, IFNULL( attend_num_school, 0 ) attend_num_school, IFNULL( attend_count_school, 0 ) attend_count_school, IFNULL( attend_num_outside, 0 ) attend_num_outside, IFNULL( attend_count_outside, 0 ) attend_count_outside, IFNULL( attend_num_teacher, 0 ) attend_num_teacher, IFNULL( attend_count_teacher, 0 ) attend_count_teacher, IFNULL( avg_total_score, 0 ) avg_total_score, IFNULL( avg_school_score, 0 ) avg_school_score, IFNULL( avg_unit_score, 0 ) avg_unit_score, IFNULL( avg_outside_score, 0 ) avg_outside_score, IFNULL( avg_teacher_score, 0 ) avg_teacher_score, concat( max_score, '(', max_teacher, ')' ) max_score, concat( min_score, '(', min_teacher, ')' ) min_score FROM db_attend attend LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num ON attend_num.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count ON attend_count.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_school, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_school ON attend_num_school.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_school, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_school ON attend_count_school.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_outside, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_outside ON attend_num_outside.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_outside, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_outside ON attend_count_outside.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_teacher, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_teacher ON attend_num_teacher.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_teacher, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_teacher ON attend_count_teacher.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_total_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) avg_total_score ON avg_total_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_school_score, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_school_score ON avg_school_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_unit_score, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_unit_score ON avg_unit_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_outside_score, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_outside_score ON avg_outside_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_teacher_score, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_teacher_score ON avg_teacher_score.listen_id = attend.listen_id LEFT JOIN ( SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.listen_id FROM ( SELECT max( score ) max_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) a LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.listen_id = a.listen_id WHERE STATUS = 2 $where1 GROUP BY db_attend.listen_id ) max_score ON max_score.listen_id = attend.listen_id LEFT JOIN ( SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.listen_id FROM ( SELECT min( score ) min_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) a LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.listen_id = a.listen_id WHERE STATUS = 2 $where1 GROUP BY db_attend.listen_id ) min_score ON min_score.listen_id = attend.listen_id $where GROUP BY attend.listen_name limit ".$_POST['pageSize']* ($_POST['pageNo']-1) .','.$_POST['pageSize']) ;

					$count=$this->Attend_model->query("SELECT count(*) count FROM db_attend attend LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num ON attend_num.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count ON attend_count.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_school, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_school ON attend_num_school.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_school, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_school ON attend_count_school.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_outside, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_outside ON attend_num_outside.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_outside, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_outside ON attend_count_outside.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_teacher, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_teacher ON attend_num_teacher.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_teacher, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_teacher ON attend_count_teacher.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_total_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) avg_total_score ON avg_total_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_school_score, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_school_score ON avg_school_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_unit_score, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_unit_score ON avg_unit_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_outside_score, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_outside_score ON avg_outside_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_teacher_score, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_teacher_score ON avg_teacher_score.listen_id = attend.listen_id LEFT JOIN ( SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.listen_id FROM ( SELECT max( score ) max_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) a LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.listen_id = a.listen_id WHERE STATUS = 2 $where1 GROUP BY db_attend.listen_id ) max_score ON max_score.listen_id = attend.listen_id LEFT JOIN ( SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.listen_id FROM ( SELECT min( score ) min_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) a LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.listen_id = a.listen_id WHERE STATUS = 2 $where1 GROUP BY db_attend.listen_id ) min_score ON min_score.listen_id = attend.listen_id $where GROUP BY attend.listen_name") ;
					$data['count']=isset($count[0]['count']) ? $count[0]['count'] : '0';
					$data["totalPage"] = ceil($data['count'] / $params['pageSize']);
					$data['pageNo'] = $params['pageNo'];	
				}					
			} else {
				if($_POST['type']==1){
					$data['ret'] = $this -> Attend_model -> query("SELECT attend.course_units, IFNULL(attend_num,0)attend_num , IFNULL(attend_count,0)attend_count , IFNULL(attend_num_school,0)attend_num_school, IFNULL(attend_count_school,0)attend_count_school, IFNULL(attend_num_outside,0)attend_num_outside, IFNULL(attend_count_outside,0)attend_count_outside,  IFNULL(attend_num_teacher,0)attend_num_teacher, IFNULL(attend_count_teacher,0)attend_count_teacher, IFNULL(avg_total_score,0)avg_total_score, IFNULL(avg_school_score,0)avg_school_score, IFNULL(avg_unit_score,0)avg_unit_score, IFNULL(avg_outside_score,0)avg_outside_score, IFNULL(avg_teacher_score,0)avg_teacher_score, concat(max_score,'(',max_teacher,')') max_score, concat(min_score,'(',min_teacher,')') min_score FROM db_attend attend LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num, course_units FROM db_attend  WHERE  type = '2' and status=2 $where1 GROUP BY course_units ) attend_num ON attend_num.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count, course_units FROM db_attend  WHERE  type = '2' and status=2 $where1 GROUP BY course_units ) attend_count ON attend_count.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1  GROUP BY course_units ) attend_num_school ON attend_num_school.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1 GROUP BY course_units ) attend_count_school ON attend_count_school.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 $where1 GROUP BY course_units ) attend_num_outside ON attend_num_outside.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 $where1 GROUP BY course_units ) attend_count_outside ON attend_count_outside.course_units = attend.course_units LEFT JOIN ( SELECT count(DISTINCT listen_id) attend_num_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 $where1 GROUP BY course_units ) attend_num_teacher ON attend_num_teacher.course_units = attend.course_units LEFT JOIN ( SELECT count(*) attend_count_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 $where1 GROUP BY course_units ) attend_count_teacher ON attend_count_teacher.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_total_score, course_units FROM db_attend  WHERE status=2 $where1 GROUP BY course_units ) avg_total_score ON avg_total_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_school_score, course_units FROM db_attend  WHERE  type='1' and status=2 $where1 GROUP BY course_units ) avg_school_score ON avg_school_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_unit_score, course_units FROM db_attend  WHERE  type='2' and status=2 $where1 GROUP BY course_units ) avg_unit_score ON avg_unit_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_outside_score, course_units FROM db_attend  WHERE  type='3' and status=2 $where1 GROUP BY course_units ) avg_outside_score ON avg_outside_score.course_units = attend.course_units LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_teacher_score, course_units FROM db_attend  WHERE  type='4' and status=2 $where1 GROUP BY course_units ) avg_teacher_score ON avg_teacher_score.course_units = attend.course_units LEFT JOIN (SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.course_units FROM ( SELECT max( score ) max_score, course_units FROM db_attend WHERE STATUS = 2 $where1 GROUP BY course_units ) a LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.course_units = a.course_units where status=2 $where1 GROUP BY db_attend.course_units) max_score ON max_score.course_units = attend.course_units LEFT JOIN (SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.course_units FROM ( SELECT min( score ) min_score, course_units FROM db_attend WHERE STATUS = 2 $where1 GROUP BY course_units ) a LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.course_units = a.course_units where status=2  $where1 GROUP BY db_attend.course_units) min_score ON min_score.course_units = attend.course_units $where GROUP BY attend.course_units ORDER BY field( dept_name, '电气电子工程分院', '机械工程分院', '建筑工程分院', '设计与艺术分院', '财经分院', '交通分院', '商贸分院', '鉴湖分院', '人文社科部')");
				}else{
					$data['ret'] = $this -> Attend_model -> query("SELECT attend.listen_name username, IFNULL( attend_num, 0 ) attend_num, IFNULL( attend_count, 0 ) attend_count, IFNULL( attend_num_school, 0 ) attend_num_school, IFNULL( attend_count_school, 0 ) attend_count_school, IFNULL( attend_num_outside, 0 ) attend_num_outside, IFNULL( attend_count_outside, 0 ) attend_count_outside, IFNULL( attend_num_teacher, 0 ) attend_num_teacher, IFNULL( attend_count_teacher, 0 ) attend_count_teacher, IFNULL( avg_total_score, 0 ) avg_total_score, IFNULL( avg_school_score, 0 ) avg_school_score, IFNULL( avg_unit_score, 0 ) avg_unit_score, IFNULL( avg_outside_score, 0 ) avg_outside_score, IFNULL( avg_teacher_score, 0 ) avg_teacher_score, concat( max_score, '(', max_teacher, ')' ) max_score, concat( min_score, '(', min_teacher, ')' ) min_score FROM db_attend attend LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num ON attend_num.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count ON attend_count.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_school, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_school ON attend_num_school.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_school, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_school ON attend_count_school.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_outside, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_outside ON attend_num_outside.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_outside, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_outside ON attend_count_outside.listen_id = attend.listen_id LEFT JOIN ( SELECT count( DISTINCT teacher_code ) attend_num_teacher, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_num_teacher ON attend_num_teacher.listen_id = attend.listen_id LEFT JOIN ( SELECT count(*) attend_count_teacher, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) attend_count_teacher ON attend_count_teacher.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_total_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) avg_total_score ON avg_total_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_school_score, listen_id FROM db_attend WHERE type = '1' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_school_score ON avg_school_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_unit_score, listen_id FROM db_attend WHERE type = '2' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_unit_score ON avg_unit_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_outside_score, listen_id FROM db_attend WHERE type = '3' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_outside_score ON avg_outside_score.listen_id = attend.listen_id LEFT JOIN ( SELECT CAST( avg( score ) AS DECIMAL ( 10, 2 )) avg_teacher_score, listen_id FROM db_attend WHERE type = '4' AND STATUS = 2 $where1 GROUP BY listen_id ) avg_teacher_score ON avg_teacher_score.listen_id = attend.listen_id LEFT JOIN ( SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.listen_id FROM ( SELECT max( score ) max_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) a LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.listen_id = a.listen_id WHERE STATUS = 2 $where1 GROUP BY db_attend.listen_id ) max_score ON max_score.listen_id = attend.listen_id LEFT JOIN ( SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.listen_id FROM ( SELECT min( score ) min_score, listen_id FROM db_attend WHERE STATUS = 2 $where1 GROUP BY listen_id ) a LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.listen_id = a.listen_id WHERE STATUS = 2 $where1 GROUP BY db_attend.listen_id ) min_score ON min_score.listen_id = attend.listen_id $where GROUP BY attend.listen_name ") ;
				}
			}

			if (!$isExport) {	
				$attend_num_unit=0;
				$attend_count_unit=0;
				$attend_num_school=0;
				$attend_count_school=0;
				$attend_num_outside=0;
				$attend_count_outside=0;
				$attend_num_teacher=0;
				$attend_count_teacher=0;
				$avg_total_score=0;
				$avg_school_score=0;
				$avg_unit_score=0;
				$avg_outside_score=0;
				$avg_teacher_score=0;
				
				foreach ($data['ret'] as $k => $v) {
					$attend_num_unit+=$v['attend_num'];
					$attend_count_unit+=$v['attend_count'];
					$attend_num_school+=$v['attend_num_school'];
					$attend_count_school+=$v['attend_count_school'];
					$attend_num_outside+=$v['attend_num_outside'];
					$attend_count_outside+=$v['attend_count_outside'];
					$attend_num_teacher+=$v['attend_num_teacher'];
					$attend_count_teacher+=$v['attend_count_teacher'];

					$avg_total_score+=$v['avg_total_score'];
					$avg_school_score+=$v['avg_school_score'];
					$avg_unit_score+=$v['avg_unit_score'];
					$avg_outside_score+=$v['avg_outside_score'];
					$avg_teacher_score+=$v['avg_teacher_score'];
					// $max_count+=$v['max'];
					// $min_count+=$v['min'];
				}

				$count = count($data['ret']);

				$data['ret'][$count]['course_units']= '合计';
				$data['ret'][$count]['attend_num']=$attend_num_unit;
				$data['ret'][$count]['attend_count']=$attend_count_unit;
				$data['ret'][$count]['attend_num_school']=$attend_num_school;
				$data['ret'][$count]['attend_count_school']=$attend_count_school;
				$data['ret'][$count]['attend_num_outside']=$attend_num_outside;
				$data['ret'][$count]['attend_count_outside']=$attend_count_outside;
				$data['ret'][$count]['attend_num_teacher']=$attend_num_teacher;
				$data['ret'][$count]['attend_count_teacher']=$attend_count_teacher;
				$data['ret'][$count]['avg_total_score']=$avg_total_score;
				$data['ret'][$count]['avg_school_score']=$avg_school_score;
				$data['ret'][$count]['avg_unit_score']=$avg_unit_score;
				$data['ret'][$count]['avg_outside_score']=$avg_outside_score;
				$data['ret'][$count]['avg_teacher_score']=$avg_teacher_score;
				$data['ret'][$count]['max_score']='';
				$data['ret'][$count]['min_score']='';
	
					if(count($data['ret'])!=0){
						// 教学水平最高分
						$sql=$this->sqlEscape("select * from db_attend where score=(SELECT max(score) from db_attend where status=?? ".$where1." ) and status=?? ".$where1." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
						$max=$this->Attend_model->query($sql);		
						if($max){
							$max_score=$max[0]['score'];
							$max_teacher_name=implode(',', array_column($max,'teacher_name'));
							$max_level=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
						}else{
							$max_level= '';
						}	
						
						//教学水平 最低分
						$sql=$this->sqlEscape("select * from db_attend where score=(SELECT min(score) from db_attend where status=?? ".$where1." ) and status=?? ".$where1." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
						$min=$this->Attend_model->query($sql);			
						if($min){
							$min_score=$min[0]['score'];
							$min_teacher_name=implode(',', array_column($min,'teacher_name'));
							$min_level=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';
						}else{
							$min_level= '';
						}
						
						$data['ret'][$count]['max_score']=$max_level;
						$data['ret'][$count]['min_score']=$min_level;
					}
										
					echo json_encode($data);

		} else {
			// var_dump($data['ret']);exit;
			// 合计
			$attend_num_unit = 0;
			$attend_count_unit = 0;
			$attend_num_school = 0;
			$attend_count_school = 0;

			$attend_num_outside = 0;
			$attend_count_outside = 0;

			$attend_num_teacher = 0;
			$attend_count_teacher = 0;

			$avg_total_score = 0; //总平均分
			$avg_school_score = 0; //校督导平均分
			$avg_unit_score = 0; //二级督导平均分
			$avg_outside_score = 0; //外聘平均分
			$avg_teacher_score = 0; //教师平均分

			 foreach ($data['ret'] as $k => $v) {
				$attend_num_unit+=$v['attend_num'];
				$attend_count_unit+=$v['attend_count'];
				$attend_num_school+=$v['attend_num_school'];
				$attend_count_school+=$v['attend_count_school'];
				$attend_num_outside+=$v['attend_num_outside'];
				$attend_count_outside+=$v['attend_count_outside'];
				$attend_num_teacher+=$v['attend_num_teacher'];
				$attend_count_teacher+=$v['attend_count_teacher'];

				$avg_total_score+=$v['avg_total_score'];
				$avg_school_score+=$v['avg_school_score'];
				$avg_unit_score+=$v['avg_unit_score'];
				$avg_outside_score+=$v['avg_outside_score'];
				$avg_teacher_score+=$v['avg_teacher_score'];
				// $max_count+=$v['max'];
				// $min_count+=$v['min'];
			}


			$newlist = array();

			 foreach ($data['ret'] as $k => $v) {
			 	if ($_POST['type'] == 1) {
			    	$newlist[$k]['attend_id'] = $k+1;
					$newlist[$k]['course_units'] = $v['course_units'];
			     }else{
			     	$newlist[$k]['teacher_code'] = $k+1;
					 $newlist[$k]['course_units'] = $v['username'];
			     }
			 	$newlist[$k]['attend_num'] = $v['attend_num'];
			 	$newlist[$k]['attend_count'] = $v['attend_count'];
			 	$newlist[$k]['attend_num_school'] = $v['attend_num_school'];
			 	$newlist[$k]['attend_count_school'] = $v['attend_count_school'];
			 	$newlist[$k]['attend_num_outside'] = $v['attend_num_outside'];
			 	$newlist[$k]['attend_count_outside'] = $v['attend_count_outside'];
			 	$newlist[$k]['attend_num_teacher'] = $v['attend_num_teacher'];
			 	$newlist[$k]['attend_count_teacher'] = $v['attend_count_teacher'];
			 	$newlist[$k]['avg_total_score'] = $v['avg_total_score'];
			 	$newlist[$k]['avg_school_score'] = $v['avg_school_score'];
			 	$newlist[$k]['avg_unit_score'] = $v['avg_unit_score'];
			 	$newlist[$k]['avg_outside_score'] = $v['avg_outside_score'];
			 	$newlist[$k]['avg_teacher_score'] = $v['avg_teacher_score'];
			 	$newlist[$k]['max_score'] = $v['max_score'];
			 	$newlist[$k]['min_score'] = $v['min_score'];
	
			 }

			 if ($_POST['type'] == 1) {
				$group1= 'attend_id';
				$group2= 'course_units';
			} else {
				$group1 = 'teacher_code';
				$group2 = 'course_units';
			}
			if (count($data['ret']) != 0) {

				// 教学水平最高分
					$sql=$this->sqlEscape("select * from db_attend where score=(SELECT max(score) from db_attend where status=?? ".$where1." ) and status=?? ".$where1." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
					$max=$this->Attend_model->query($sql);		
					if($max){
						$max_score=$max[0]['score'];
						$max_teacher_name=implode(',', array_column($max,'teacher_name'));
						$max_level=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
					}else{
						$max_level= '';
					}	
					
					//教学水平 最低分
					$sql=$this->sqlEscape("select * from db_attend where score=(SELECT min(score) from db_attend where status=?? ".$where1." ) and status=?? ".$where1." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
					$min=$this->Attend_model->query($sql);			
					if($min){
						$min_score=$min[0]['score'];
						$min_teacher_name=implode(',', array_column($min,'teacher_name'));
						$min_level=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';
					}else{
						$min_level= '';
					}


				array_push($newlist, array($group1=>count($data['ret'])+1,$group2 => '合计', '' => '', 'attend_num' => $attend_num_unit, 'attend_count' => $attend_count_unit, 'attend_num_school' => $attend_num_school, 'attend_count_school' => $attend_count_school, 'attend_num_outside' => $attend_num_outside, 'attend_count_outside' => $attend_count_outside, 'attend_num_teacher' => $attend_num_teacher, 'attend_count_teacher' => $attend_count_teacher, 'avg_total_score' => $avg_total_score,'avg_unit_score' => $avg_unit_score, 'avg_school_score' => $avg_school_score,  'avg_outside_score' => $avg_outside_score, 'avg_teacher_score' => $avg_teacher_score, 'max_score' => $max_level, 'min_score' => $min_level));

			} else {

				array_push($newlist, array($group1=>0,$group2 => '合计', '' => '', 'attend_num' => 0, 'attend_count' => 0, 'attend_num_school' => 0, 'attend_count_school' => 0, 'attend_num_outside' => 0, 'attend_count_outside' => 0, 'attend_num_teacher' => 0, 'attend_count_teacher' => 0, 'avg_total_score' => 0, 'avg_unit_score' => 0, 'avg_school_score' => 0, 'avg_outside_score' => 0, 'avg_teacher_score' => 0, 'max_score' => 0, 'min_score' => 0));

			}


			$this->load->library('PHPExcel/PHPExcel');
			$excel = new PHPExcel();
			//公共样式
			$excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
			$excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

			$x_line = 0;
			$y_line = 1;

			//标题
			// $title=array($_POST['start_time'].'至'.$_POST['end_time'],'被听人数','被听人次','分院督导听课人数','分院督导听课次数','校督导听课人数','校督导听课次数','平均分','90分(含)以上','80分以下','最高分','最低分');
			if ($_POST['type'] == 1) {
				$group = '开课单位';
				$group1= 'attend_id';
				$group2= 'course_units';
			} else {
				$group = '教师姓名';
				$group1 = 'teacher_code';
				$group2 = 'course_units';
			}
			if(count($role)==1 && $role[0]==WebRole::Teacher[XPHEnum::Value]){
				$title = array($_POST['start_time'] . '至' . $_POST['end_time'], $group,'教师听课人数', '教师听课次数', '教师平均分', '总分最高分', '总分最低分');
				$items=array($group1, $group2, 'attend_num_teacher', 'attend_count_teacher', 'avg_teacher_score', 'max_score', 'min_score');
			}else{
				$title = array($_POST['start_time'] . '至' . $_POST['end_time'], $group, '二级督导听课人数', '二级督导听课次数', '校督导听课人数', '校督导听课次数', '外聘督导听课人数', '外聘督导听课次数','教师听课人数', '教师听课次数',  '总分平均分', '二级督导平均分', '校督导平均分', '外聘督导平均分', '教师平均分', '总分最高分', '总分最低分');
				$items=array($group1, $group2, 'attend_num', 'attend_count', 'attend_num_school', 'attend_count_school', 'attend_num_outside', 'attend_count_outside', 'attend_num_teacher', 'attend_count_teacher', 'avg_total_score', 'avg_school_score', 'avg_unit_score', 'avg_outside_score', 'avg_teacher_score', 'max_score', 'min_score');
			}

			// if(isset($_POST['type'] ) && $_POST['type']==1){
			// 	$title=array($_POST['start_time'].'至'.$_POST['end_time'],$group,'督导听课人数','督导听课次数','校督导听课人数','校督导听课次数','总分最高分','总分最低分');
			// }else if(isset($_POST['type'] ) && $_POST['type']==2){
			// 	$title=array($_POST['start_time'].'至'.$_POST['end_time'],$group,'督导听课人数','督导听课次数','校督导听课人数','校督导听课次数','外聘督导听课人数','外聘督导听课次数','总分平均分','校督导平均分','二级督导平均分','外聘督导平均分','总分最高分','总分最低分');
			// }
			foreach ($title as $k => $v) {
				$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $v);
				$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line)->getFont()->setBold(true);
				$x_line++;
			}

			$x_line = 0;
			$y_line = 2;
			//正式数据
			foreach ($newlist as $k => $v) {
				foreach ($v as $k1 => $v1) {
					// if($k1=='id'){
					//  $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$_POST['start_time'].'至'.$_POST['end_time']);
					// }else{
					if ($k1 == 'id') {
						continue;
					}
					if(in_array($k1,$items)){
						$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $v1);
						$x_line++;
					}

				}
				$x_line = 0;
				$y_line++;
			}
			

			$write = new PHPExcel_Writer_Excel2007($excel);
			$director = "upload/export/" . date('Ymd', time()) . "/";
			if (!file_exists($director)) {
				//检查是否有该文件夹，如果没有就创建，并给予最高权限
				mkdir($director, 0777);
			}
			$file_name = '听课数据分析表' . date('Ymd') . '.xlsx';
			$write->save($director . iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址', true, $director . $file_name));
		}
		
	}

	// 获取被听课教师列表
	/**
	 * @param course_units 开课单位
	 * @param start_time 开始时间
	 * @param end_time 结束时间
	 */
	function getTeacherList($select,$type,$course_units,$listen_type,$source,$start_time=false,$end_time=false,$isExport=false){
		if($isExport){//是否导出
			$select = $_POST['select'];
			$type = $_POST['type'];
			$course_units = $_POST['course_units'];
			$listen_type = $_POST['listen_type'];
			$start_time = $_POST['start_time'];
			$end_time = $_POST['end_time'];
		}

		$course_units=urldecode($course_units);
		$listen_type=urldecode($listen_type);

		if($start_time && !is_numeric($start_time)){
			$start_time=strtotime(urldecode($start_time)) ;
		}
		if($end_time && !is_numeric($end_time)){
			$end_time=strtotime(urldecode($end_time));
		}	
		$role=explode(',',getSess()['sub_role_id']);
		array_push($role,getSess()['role_id']);
		$where_role = '';
		if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)|| in_array(WebRole::UnitLeader[XPHEnum::Value], $role) ){
			$where_role=' and 1=1';
		}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){//二级督导组长
			if (!in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) && !in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) && !in_array(WebRole::OutsideSupervisor[XPHEnum::Value], $role) && !in_array(WebRole::UnitLeader[XPHEnum::Value], $role)){
			        $where_role = $this->sqlEscape(' and db_admin.dept_id=?? ',array(getSess()['dept_id']));
				}
		} else if (in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::OutsideSupervisor[XPHEnum::Value], $role) || in_array(WebRole::UnitLeader[XPHEnum::Value], $role)) {
				$where_role = $this->sqlEscape(' and db_admin.usercode=??', array(getSess()['usercode']));
		}else{
			$where_role=' and 1=1';
		}

		// pri($where_role);
		// pri('数据分析2');
		
		if ($select == '1') {
  	       $connar['where'] = $this->sqlEscape("db_attend.status=?? and db_attend.course_units=?? ", array(AttendStatus::Submit[XPHEnum::Value], $course_units));
			$connar['join'] = array('db_admin', 'db_admin.id=db_attend.listen_id');
			$connar['orderby'] = "CONVERT(teacher_name using gbk);";
			if ($type == 1) {
				$connar['items'] = "teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::Unit[XPHEnum::Value]));
			} else if ($type == 2) {
				$connar['items'] = "teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::School[XPHEnum::Value]));
			}else if ($type == 3) {
				$connar['items'] = "teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::Outside[XPHEnum::Value]));
			}else if ($type == 4) {
				$connar['items'] = "teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::Teacher[XPHEnum::Value]));
			} 

		} else {
			$connar['where'] = $this->sqlEscape("db_attend.status=?? and db_admin.username=?? ", array(AttendStatus::Submit[XPHEnum::Value], $course_units));
			$connar['orderby'] = "listen_id";
			$connar['join'] = array('db_admin', 'db_admin.id=db_attend.listen_id');
			if ($type == 1) {
				$connar['items'] = "listen_name ,db_admin.usercode as listen_code,db_attend.score score,teacher_name,teacher_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::Unit[XPHEnum::Value]));
			} else if ($type == 2) {
				$connar['items'] = "listen_name ,db_admin.usercode as listen_code,db_attend.score score,teacher_name,teacher_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::School[XPHEnum::Value]));
			}else if ($type ==3) {
				$connar['items'] = "listen_name ,db_admin.usercode as listen_code,db_attend.score score,teacher_name,teacher_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::Outside[XPHEnum::Value]));
			}else if ($type == 4) {
				$connar['items'] = "listen_name ,db_admin.usercode as listen_code,db_attend.score score,teacher_name,teacher_code";
				$connar['where'] .= $this->sqlEscape("and type=??", array(AttendType::Teacher[XPHEnum::Value]));
			}
		}


		// if($select == '1'){
	 //    	$connar['where']=$this->sqlEscape("db_attend.status=?? and course_units=?? ",array(AttendStatus::Submit[XPHEnum::Value],$course_units));
		// }else{
		// 	$connar['where'] = $this->sqlEscape("db_attend.status=?? and teacher_name=?? ", array(AttendStatus::Submit[XPHEnum::Value], $course_units));
		// }


		// $connar['orderby']="CONVERT(teacher_name using gbk);";
		// $connar['join']=array('db_admin','db_admin.id=db_attend.listen_id');
		// if($type==1){
		// 	$connar['items']="teacher_code,teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";	
		// 	$connar['where'].=$this->sqlEscape("and type=??",array(AttendType::Unit[XPHEnum::Value]));
		// }else if($type==2){
		// 	$connar['items']="teacher_code,teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";	
		// 	$connar['where'].=$this->sqlEscape("and type=??",array(AttendType::School[XPHEnum::Value]));
		// }else if($type==3){
		// 	$connar['items']="teacher_code,teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";	
		// 	$connar['where'].=$this->sqlEscape("and type=??",array(AttendType::Outside[XPHEnum::Value]));
		// }else if($type==4){
		// 	$connar['items']="teacher_code,teacher_name,teacher_code,score,listen_name,db_admin.usercode listen_code";	
		// 	$connar['where'].=$this->sqlEscape("and type=??",array(AttendType::Teacher[XPHEnum::Value]));
		// }
		// else if($type==5){
		// 	$connar['where'].=$this->sqlEscape("and teaching_level_score>=90",array());
		// }else if($type==6){
		// 	$connar['where'].=$this->sqlEscape("and teaching_level_score<80",array());
		// }else if($type==7){
		// 	$connar['where'].=$this->sqlEscape("and teaching_effect_score>=90",array());
		// }else if($type==8){
		// 	$connar['where'].=$this->sqlEscape("and teaching_effect_score<80",array());
		// }
		// if (isset($params['start_time']) && isset($params['end_time'])) {
		// 	$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
		// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
		// 	$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
		// }
		if($start_time){
			$connar['where'].=$this->sqlEscape(' and listen_time>??',array($start_time.' 00:00:00'));
		}
		if($end_time){
			$connar['where'].=$this->sqlEscape(' and listen_time<??',array($end_time." 23:59:59"));
		}

		if($listen_type != '-1'){
			$connar['where'].=$this->sqlEscape(' and listen_type = ??',array($listen_type));
		}

		$connar['where'].=$where_role;
		$data['list']=$this->Attend_model->get_list_full($connar);

		// echo $this->db->last_query();

		$data['newlist']=array();
		foreach ($data['list'] as $k => $v) {
			$data['newlist'][$v['teacher_code']][]=$v;
		}
		// pri($data['newlist']);


	

		if($type==1){
			$data['title']="二级督导听课人次";
		}else if($type==2){
			$data['title']="校督导听课人次";
		}else if($type==3){
			$data['title']="外聘督导听课人次";	
			
		}else if($type==4){
			$data['title']="教师听课人次";
			
		}
		// else if($type==5){
		// $data['title']="教学水平90分(含)以上"; 	
		// }else if($type==6){
		// $data['title']="教学效果90分(含)以上";
		// }else if($type==7){
		// 	$data['title']="教学效果80以下";
		// }else if($type==8){
		// $data['title']="教学水平80以下";
		// }
		//导出
	   if($isExport){

		   $this-> load ->library('PHPExcel/PHPExcel');
            $excel = new PHPExcel();
            //公共样式
            $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

            $x_line=0;
            $y_line=1;

            //正式开始
            $excel = new PHPExcel();
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);

              //标题
            $excel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex(0).'1:'.PHPExcel_Cell::stringFromColumnIndex(4).'1');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).'1',$data['title']);
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $i=2;
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,'序号');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,'授课教师');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,'分数');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$i,'听课教师');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(4).$i,'平均分');
              
        $i++;
        foreach ($data['newlist'] as $k => $v) {
			$start_i=$i;
           foreach ($v as $k1 => $v1) {
			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,$i-2);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,$v1['teacher_code']."(".$v1['teacher_name'].")");
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,$v1['score']); 
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$i,$v1['listen_name']); 
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(4).$i,sprintf("%.2f",array_sum(array_column($v,'score'))/count($v)));           
            $i++;
		   }
		   
		   $excel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex(1).$start_i.':'.PHPExcel_Cell::stringFromColumnIndex(1).($i-1));
		   $excel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex(4).$start_i.':'.PHPExcel_Cell::stringFromColumnIndex(4).($i-1));
        }
        //设置对齐
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(9).($i-1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(0).($i-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


            $write = new PHPExcel_Writer_Excel2007($excel); 
            $director = "upload/export/" . date('Ymd', time()) . "/";
            if (!file_exists($director)) {
                //检查是否有该文件夹，如果没有就创建，并给予最高权限
                mkdir($director, 0777);
            }        
            $file_name =$data['title'].date('Ymd').'.xlsx';           
            $write->save($director.iconv('utf-8', 'gb2312', $file_name));
            exit(retJson('返回地址',true,$director.$file_name));

	   }else{
	  	 	$data['params'] = array('select'=>$select,'type'=>$type,'source'=>$source,'course_units'=>$course_units,'listen_type'=>$listen_type,'start_time'=>$start_time,'end_time'=>$end_time);
	   	    $this->load->view('admin/attendDept',$data);
	   }

	}
	
}



